package hee;

import hee.model.SungJuk2VO;

import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.InputMismatchException;
import java.util.Scanner;

import oracle.jdbc.proxy.annotation.Pre;
        //메소드로
public class J22SungJuk {
	//성적처리를 JDBC로 구현
	//오라클 데이터베이스를 이용해서
	//성적 데이터 저장, 조회, 수정, 삭제를 처리
	
	//JDBC 객체 및 연결정보
	//데이타베이스 접속
	private static String DRV="oracle.jdbc.driver.OracleDriver"; //드라이버 명
	private static String URL="jdbc:oracle:thin:@112.216.253.124:1521:xe";//주소 
	private static String USR="victorlosa"; 
	private static String PWD="victorlosa"; 
	
	public static void main(String[] args) {
	    //성적데이터 입력
		System.out.println(newSungJuk());
		//구분선
		System.out.println("-----------------------");
		//성적데이터 전체출력
		System.out.println(readSungJuk());
		//구분선
	    System.out.println("-----------------------");
		//성적데이터 검색
	    System.out.println(readOneSungJuk());
		
	}//main

	 //성적 데이타 입력
	public static String newSungJuk(){
		
		Connection conn=null;
		PreparedStatement pstmt =null;
		
        String result="";
        String sql=" insert into sungjuk(sjno,name,kor,eng,mat,tot,avgs,grd) "
        + "values(sjno_seq.nextval,?,?,?,?,?,?,?) ";
        Scanner sc = new Scanner(System.in);
        
        System.out.println("성적데이터를 입력합니다");
        System.out.println("이름,국어,영어,수학순으로 입력하세요");
        SungJuk2VO sj = new SungJuk2VO(
        sc.next(),sc.nextInt(),sc.nextInt(),sc.nextInt());
        
        //총점,평균,학점 계산,
        computeSungJuk(sj);
        
        //sungjuk 테이블데이터 입력
        try{
        	Class.forName(DRV);
        	conn= DriverManager
        			.getConnection(URL,USR,PWD);
        	pstmt =conn.prepareStatement(sql);
        	pstmt.setString(1,sj.getName());
        	pstmt.setInt(2,sj.getKor());
        	pstmt.setInt(3,sj.getEng());
        	pstmt.setInt(4,sj.getMat());
        	pstmt.setInt(5,sj.getTot());
        	pstmt.setDouble(6,sj.getAvg());
        	pstmt.setString(7,String.valueOf(sj.getGrd()));
        	
        	int cnt=pstmt.executeUpdate();
        	
        	if(cnt>0)result=cnt + "데이터가 입력됨";
        	
        }catch(Exception ex){
        	ex.printStackTrace();
        	
        }finally{
	        if (pstmt!=null)try{pstmt.close();}catch(Exception e){}
		    if (conn !=null)try{conn.close();}catch(Exception e){}
        }
        
        
        
		return result;
	}
	
	//성적 데이타 전체조회
	public static String readSungJuk(){
		
		Connection conn=null;
		PreparedStatement pstmt = null;
		ResultSet rs=null;//select문 실행시 사용.
		String sql=" select * from sungjuk ";
		sql+=" order by sjno ";
		StringBuilder sb=new StringBuilder();
		
		System.out.println("전체 성적 데이터 출력합니다.");
		try{
			Class .forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			pstmt =conn.prepareStatement(sql);
			
			rs=pstmt.executeQuery();
			
			sb.append("==================").append("\n");
			while(rs.next()){
				sb.append(rs.getString(1)).append("/")
				   .append(rs.getString(2)).append("/")
				   .append(rs.getString(3)).append("/")
				   .append(rs.getString(4)).append("/")
				   .append(rs.getString(5)).append("/")
				   .append(rs.getString(6)).append("/")
				   .append(rs.getString(7)).append("/")
				   .append(rs.getString(8)).append("/")
				   .append(rs.getString(9)).append("\n");
				
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null)try{} catch(Exception e){}
			if(pstmt !=null)try{pstmt.close();} catch(Exception e){}
			if(rs!=null)try{conn.close();} catch(Exception e){}
		}
			
	
		
		return sb.toString();
	}
	
	


	//성적 데이타 검색
	public static String readOneSungJuk(){
		Connection conn=null;        //필수
		PreparedStatement pstmt =null;//필수
		ResultSet rs = null;         //필수
		String sql=" Select * from sungjuk ";//필수, sqp 변수
		sql +=" where name = ? order by sjno "; //필수
		StringBuilder sb=new StringBuilder();
		Scanner sc=new Scanner(System.in);//키보드 입력
		
		System.out.println("이름으로 성적데이터를 조회합니다");
		System.out.println("조회할 성적테이터를 입력합니다");
		String name =sc.next();//name변수에 담는다.
		try{//sql문 받아 오려면
			Class.forName(DRV);
			conn=DriverManager
			.getConnection(URL,USR,PWD);
		 pstmt=conn.prepareStatement(sql);
			 pstmt.setString(1,name);//이거 쓰는 이유? sql 물음표로 구분함.
			 
	     rs=pstmt.executeQuery();
			while(rs.next()){
				 sb.append(rs.getString(1)).append("/")
				   .append(rs.getString(2)).append("/")
				   .append(rs.getString(3)).append("/")
				   .append(rs.getString(4)).append("/")
				   .append(rs.getString(5)).append("/")
				   .append(rs.getString(6)).append("/")
				   .append(rs.getString(7)).append("/")
				   .append(rs.getString(8)).append("/")
				   .append(rs.getString(9)).append("\n");
					
				}
			
		}catch(Exception ex){
        	ex.printStackTrace();
        	
        }finally{
	        if (pstmt!=null)try{pstmt.close();}catch(Exception e){}
		    if (conn !=null)try{conn.close();}catch(Exception e){}
        }
		
		return sb.toString();
	}
	
	//성적 데이타 수정
	//modify : 수정
	public static String modifySungJuk(String name){
		String result ="";
		return result;
	}
	
	//성적 데이타 삭제
	//remove : 삭제
	public static String removeSungJuk(String name){
		
		
		String result ="";
				
		return result;
	}
	//성적 데이터 총점,평균,학점 계산
	public static void computeSungJuk(SungJuk2VO sj){
		String dmft="###.##";
		sj.setTot(sj.getKor() + sj.getEng() +sj.getMat());
		DecimalFormat df=new DecimalFormat(dmft);  //문자로 나타내준다 dfmt1 //날짜 출력 ~~ 포멧썼는데..
		String avg = df.format((double)sj.getTot()/3);  //결과받기
		sj.setAvg(Double.parseDouble(avg)); //문자를 실수로 parse
		
		
		sj.setAvg((double)sj.getTot()/3);
		switch((int)sj.getAvg()){
		case 10 : case 9: sj.setGrd('수'); break;
		case 8 :          sj.setGrd('우'); break;
		case 7 :          sj.setGrd('미'); break;
		case 6 :          sj.setGrd('양'); break;
	            default : sj.setGrd('가');	
		   
		detault : sj.setGrd('가');
		}
	}
	
	
}//class


















